﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    using DAL.Entities;

    public class ThongkeDAL : BaseDAL, UTL.IBaseDAL
    {
        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            throw new NotImplementedException();
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            throw new NotImplementedException();
        }

        public bool Insert(object obj)
        {
            throw new NotImplementedException();
        }

        public bool Update(object obj)
        {
            throw new NotImplementedException();
        }

        public bool DeleteOBJ(object obj)
        {
            throw new NotImplementedException();
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        public DataTable select_trangthai()
        {
            string sql = @"select distinct a.Ma_TrTh as id, Ten_trth as ten from TRANGTHAI a join HOPDONGDIEN b on a.Ma_TrTh=b.ma_trth";
            return ExecuteQuery(sql);
        }
        public DataTable select_KH_theo_trangthai(string id)
        {
            string sql = @"select a.Ma_HD,a.NgayLap,b.Ten_KH,b.DiaChi,b.CMND,b.SDT,b.NgaySinh,d.DienGiai as mucdichsudung,c.Ten_trth as trangthai
                from HOPDONGDIEN a join KHACHHANG b on a.Ma_KH=b.Ma_KH join TRANGTHAI c on a.ma_trth=c.Ma_TrTh join MUCDICHSUDUNG d on a.Ma_MD=d.Ma_MD
                where a.ma_trth='{0}'";
            sql = string.Format(sql, id);
            return ExecuteQuery(sql);
        }
        
        public DataTable select_trangthai1()
        {
            string sql = @"select distinct a.Ma_TrTh as id, Ten_trth as ten from TRANGTHAI a join hoadontientien b on a.Ma_TrTh=b.ma_trth";
            return ExecuteQuery(sql);
        }

        public DataTable select_KH_trangthaidongtien(string id)
        {
            string sql = @"select d.Ten_KH,d.DiaChi,a.TongTien,a.Ma_HDTD,a.NgayGhi,b.Ten_trth as trangthai,e.DienGiai as mucdichsudung
                        from HOADONTIENTIEN a join TRANGTHAI b on a.Ma_TrTh=b.Ma_TrTh join HOPDONGDIEN c on a.Ma_HD=c.Ma_HD join KHACHHANG d on c.Ma_KH=d.Ma_KH join MUCDICHSUDUNG e on c.Ma_MD=e.Ma_MD
                        where b.ma_trth='{0}'";
            sql = string.Format(sql, id);
            return ExecuteQuery(sql);
        }
        public DataTable select_TK_hoadon_thang(DateTime ngay)
        {
            string sql = @"select a.Ma_HDTD,a.NgayGhi,a.TongTien,a.TongKyDien,c.Ten_KH,c.DiaChi,d.Ten_trth from HOADONTIENTIEN a join HOPDONGDIEN b on a.Ma_HD=b.Ma_HD join KHACHHANG c on b.Ma_KH=c.Ma_KH join TRANGTHAI d on a.Ma_TrTh=d.Ma_TrTh where month(a.NgayGhi)=month('{0}') and year(a.NgayGhi)=year('{0}')";
            sql = string.Format(sql, ngay);
            return ExecuteQuery(sql);
        }
    }
}
